//---------------------------------------------------------------------
// File: OracleDatabaseHelperEx.cs
// 
// Summary: 
//
// Copyright (c) http://bizunitextensions.codeplex.com. All rights reserved.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
// KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
// PURPOSE.
//---------------------------------------------------------------------

using System;
using System.Data;
using System.Data.OracleClient;

namespace BizUnit.Extensions.Oracle
{
    /// <summary>
    /// Static Helper for executing SQL statements against Oracle
    /// Requires the ODP.NET provider
    /// </summary>
    public class OracleDatabaseHelperEx
    {
        #region constructor(s)
        /// <summary>
        /// Constructor for class, default constructor is private to prevent instances being
        /// created as the class only has static methods
        /// </summary>
        public OracleDatabaseHelperEx()
        {
        }
        #endregion

        #region Static Methods
        /// <summary>
        /// Excecutes the SQL statement against the database and returns a DataSet with the results
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>DataSet with the results of the executed command</returns>
        public DataSet ExecuteSqlCommand(string connectionString, string sqlCommand)
        {
            DataSet ds = new DataSet();
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    OracleDataAdapter adapter = new OracleDataAdapter(sqlCommand, connection);
                    adapter.Fill(ds);
                }   // connection
            }
            catch (Exception)
            {
                throw;
            }
            return ds;
        }

        /// <summary>
        /// Executes the SQL statement and returns the first column of the first row in the resultset returned by the query.
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        /// <returns>The contents of the first column of the first row in the resultset</returns>
        public int ExecuteScalar(string connectionString, string sqlCommand)
        {
            OracleConnection connection = null;
            object col = 0;

            try
            {
                connection = new OracleConnection(connectionString);
                OracleCommand command = new OracleCommand(sqlCommand, connection);
                command.Connection.Open();
                col = command.ExecuteScalar();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return Convert.ToInt32(col);
        }

        /// <summary>
        /// Executes the SQL statement
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="sqlCommand">SQL statement to execute</param>
        public void ExecuteNonQuery(string connectionString, string sqlCommand)
        {
            OracleConnection connection = null;

            try
            {
                connection = new OracleConnection(connectionString);
                OracleCommand command = new OracleCommand(sqlCommand, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
        #endregion
    }
}
